#! /bin/bash

/usr/bin/hive -S -e "
drop database if exists ods;
create database if not exists ods;
drop database if exists dwd;
create database if not exists dwd;
drop database if exists dwb;
create database if not exists dwb;
drop database if exists dws;
create database if not exists dws;
drop database if exists dm;
create database if not exists dm;

drop table if exists ods.t_customer_relationship;
CREATE TABLE ods.t_customer_relationship
(
    id                          INT,
    create_date_time            string,
    update_date_time            string COMMENT '最后更新时间',
    deleted                     string COMMENT '是否被删除（禁用）',
    customer_id                 INT COMMENT '所属客户id',
    first_id                    INT COMMENT '第一条客户关系id',
    belonger                    INT COMMENT '归属人',
    belonger_name               string COMMENT '归属人姓名',
    initial_belonger            INT COMMENT '初始归属人',
    distribution_handler        INT COMMENT '分配处理人',
    business_scrm_department_id int COMMENT '归属部门',
    last_visit_time             string COMMENT '最后回访时间',
    next_visit_time             string COMMENT '下次回访时间',
    origin_type                 string COMMENT '数据来源',
    itcast_school_id            INT COMMENT '校区Id',
    itcast_subject_id           INT COMMENT '学科Id',
    intention_study_type        string COMMENT '意向学习方式',
    anticipat_signup_date       string COMMENT '预计报名时间',
    level                       string COMMENT '客户级别',
    creator                     INT COMMENT '创建人',
    current_creator             INT COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
    creator_name                string COMMENT '创建者姓名',
    origin_channel              string COMMENT '来源渠道',
    comment                     string,
    first_customer_clue_id      INT COMMENT '第一条线索id',
    last_customer_clue_id       INT COMMENT '最后一条线索id',
    process_state               string COMMENT '处理状态',
    process_time                string COMMENT '处理状态变动时间',
    payment_state               string COMMENT '支付状态',
    payment_time                string COMMENT '支付状态变动时间',
    signup_state                string COMMENT '报名状态',
    signup_time                 string COMMENT '报名时间',
    notice_state                string COMMENT '通知状态',
    notice_time                 string COMMENT '通知状态变动时间',
    lock_state                  string COMMENT '锁定状态',
    lock_time                   string COMMENT '锁定状态修改时间',
    itcast_clazz_id             INT COMMENT '所属ems班级id',
    itcast_clazz_time           string COMMENT '报班时间',
    payment_url                 string COMMENT '付款链接',
    payment_url_time            string COMMENT '支付链接生成时间',
    ems_student_id              INT COMMENT 'ems的学生id',
    delete_reason               string COMMENT '删除原因',
    deleter                     INT COMMENT '删除人',
    deleter_name                string COMMENT '删除人姓名',
    delete_time                 string COMMENT '删除时间',
    course_id                   INT COMMENT '课程ID',
    course_name                 string COMMENT '课程名称',
    delete_comment              string COMMENT '删除原因说明',
    close_state                 string COMMENT '关闭装填',
    close_time                  string COMMENT '关闭状态变动时间',
    appeal_id                   INT COMMENT '申诉id',
    tenant                      INT COMMENT '租户',
    total_fee                   string COMMENT '报名费总金额',
    belonged                    INT COMMENT '小周期归属人',
    belonged_time               string COMMENT '归属时间',
    belonger_time               string COMMENT '归属时间',
    transfer                    INT COMMENT '转移人',
    transfer_time               string COMMENT '转移时间',
    follow_type                 INT COMMENT '分配类型，0-自动分配，1-手动分配，2-自动转移，3-手动单个转移，4-手动批量转移，5-公海领取',
    transfer_bxg_oa_account     string COMMENT '转移到博学谷归属人OA账号',
    transfer_bxg_belonger_name  string COMMENT '转移到博学谷归属人OA姓名'
) comment '客户意向表'
    partitioned by (dt string)
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress' = 'ZLIB');

drop table if exists ods.t_employee;
CREATE TABLE ods.t_employee
(
    id                  INT,
    email               string COMMENT '公司邮箱，OA登录账号',
    real_name           string COMMENT '员工的真实姓名',
    phone               string COMMENT '手机号，目前还没有使用；隐私问题OA接口没有提供这个属性，',
    department_id       string COMMENT 'OA中的部门编号，有负值',
    department_name     string COMMENT 'OA中的部门名',
    remote_login        string COMMENT '员工是否可以远程登录',
    job_number          string COMMENT '员工工号',
    cross_school        string COMMENT '是否有跨校区权限',
    last_login_date     string COMMENT '最后登录日期',
    creator             INT COMMENT '创建人',
    create_date_time    string COMMENT '创建时间',
    update_date_time    string COMMENT '最后更新时间',
    deleted             string COMMENT '是否被删除（禁用）',
    scrm_department_id  INT COMMENT 'SCRM内部部门id',
    leave_office        string COMMENT '离职状态',
    leave_office_time   string COMMENT '离职时间',
    reinstated_time     string COMMENT '复职时间',
    superior_leaders_id INT COMMENT '上级领导ID',
    tdepart_id          INT COMMENT '直属部门',
    tenant              INT,
    ems_user_name       string
) comment '员工信息表'
    partitioned by (dt string)
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress' = 'ZLIB');

drop table if exists ods.t_itcast_clazz;
CREATE TABLE ods.t_itcast_clazz
(
    id                  INT COMMENT 'ems课程id(非自增)',
    create_date_time    string COMMENT '创建时间',
    update_date_time    string COMMENT '最后更新时间',
    deleted             string COMMENT '是否被删除（禁用）',
    itcast_school_id    string COMMENT 'ems校区ID',
    itcast_school_name  string COMMENT 'ems校区名称',
    itcast_subject_id   string COMMENT 'ems学科ID',
    itcast_subject_name string COMMENT 'ems学科名称',
    itcast_brand        string COMMENT 'ems品牌',
    clazz_type_state    string COMMENT '班级类型状态',
    clazz_type_name     string COMMENT '班级类型名称',
    teaching_mode       string COMMENT '授课模式',
    start_time          string COMMENT '开班时间',
    end_time            string COMMENT '毕业时间',
    comment             string COMMENT '备注',
    detail              string COMMENT '详情(比如：27期)',
    uncertain           string COMMENT '待定班(0:否,1:是)',
    tenant              INT
) comment '班级信息表'
    partitioned by (dt string)
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress' = 'ZLIB');

drop table if exists ods.t_scrm_department;
CREATE TABLE ods.t_scrm_department
(
    id               INT COMMENT '部门id',
    name             string COMMENT '部门名称',
    parent_id        INT COMMENT '父部门id',
    create_date_time string COMMENT '创建时间',
    update_date_time string COMMENT '更新时间',
    deleted          string COMMENT '删除标志',
    id_path          string COMMENT '编码全路径',
    tdepart_code     INT COMMENT '直属部门',
    creator          string COMMENT '创建者',
    depart_level     INT COMMENT '部门层级',
    depart_sign      INT COMMENT '部门标志，暂时默认1',
    depart_line      INT COMMENT '业务线，存储业务线编码',
    depart_sort      INT COMMENT '排序字段',
    disable_flag     INT COMMENT '禁用标志',
    tenant           INT
) comment '员工部门表'
    partitioned by (dt string)
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress' = 'ZLIB');

drop table if exists ods.t_customer_appeal;
create table if not exists ods.t_customer_appeal
(
    id                             int comment '主键',
    customer_relationship_first_id int comment '第一条客户关系id',
    employee_id                    int comment '申诉人',
    employee_name                  string comment '申诉人姓名',
    employee_department_id         int comment '申诉人部门',
    employee_tdepart_id            int comment '申诉人所属部门',
    appeal_status                  int comment '申诉状态，0:待稽核 1:无效 2：有效',
    audit_id                       int comment '稽核人id',
    audit_name                     string comment '稽核人姓名',
    audit_department_id            int comment '稽核人所在部门',
    audit_department_name          string comment '稽核人部门名称',
    audit_date_time                string comment '稽核时间',
    create_date_time               string comment '创建时间（申诉时间）',
    update_date_time               string comment '更新时间',
    deleted                        string comment '删除标志位',
    tenant                         int
) comment '线索申诉信息表'
    partitioned by (dt string)
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress' = 'ZLIB');

drop table if exists dwd.fact_customer_relationship;
create table if not exists dwd.fact_customer_relationship
(
    id                int,
    create_date_time  string,
    appeal_status     int,
    origin_type       string,
    origin_channel    string,
    payment_state     int,
    payment_time      string,
    itcast_clazz_id   int,
    itcast_school_id  string,
    itcast_subject_id string,
    creator           int,
    dt                string
) comment '报名基础信息事实表'
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress' = 'SNAPPY');

drop table if exists dwd.dim_clazz;
create table if not exists dwd.dim_clazz
(
    clazz_id            int,
    itcast_school_id    string,
    itcast_school_name  string,
    itcast_subject_id   string,
    itcast_subject_name string,
    dt                  string
) comment '课程维度表'
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress' = 'SNAPPY');

drop table if exists dwd.dim_department;
create table if not exists dwd.dim_department
(
    employee_id     int,
    department_name string,
    dt              string
) comment '咨询中心维度表'
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress' = 'SNAPPY');

drop table if exists dwb.width_sign;
create table if not exists dwb.width_sign
(
    id                  int,
    appeal_status       int,
    origin_type         string,
    origin_channel      string,
    payment_state       int,
    payment_time        string,
    itcast_school_id    string,
    itcast_school_name  string,
    itcast_subject_id   string,
    itcast_subject_name string,
    department_name     string,
    create_date_time    string,
    dt                  string
) comment '报名主题宽表'
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress' = 'SNAPPY');

drop table if exists dws.day_sign;
create table if not exists dws.day_sign
(
    day                 string,
    origin_type         string,
    itcast_school_id    string,
    itcast_school_name  string,
    itcast_subject_id   string,
    itcast_subject_name string,
    origin_channel      string,
    department_name     string,
    pay_count           bigint,
    relationship_count  bigint,
    valid_count         bigint,
    group_type          string
) comment '报名主题细粒度（天）表'
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress' = 'SNAPPY');

drop table if exists dm.month_sign;
create table if not exists dm.month_sign
(
    month               string,
    origin_type         string,
    itcast_school_id    string,
    itcast_school_name  string,
    itcast_subject_id   string,
    itcast_subject_name string,
    origin_channel      string,
    department_name     string,
    pay_count           bigint,
    relationship_count  bigint,
    valid_count         bigint,
    group_type          string
) comment '报名主题细粒度（天）表'
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress' = 'SNAPPY');

drop table if exists dm.year_sign;
create table if not exists dm.year_sign
(
    year                 string,
    origin_type         string,
    itcast_school_id    string,
    itcast_school_name  string,
    itcast_subject_id   string,
    itcast_subject_name string,
    origin_channel      string,
    department_name     string,
    pay_count           bigint,
    relationship_count  bigint,
    valid_count         bigint,
    group_type          string
) comment '报名主题细粒度（天）表'
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ('orc.compress' = 'SNAPPY');

    "